/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sql;

import data.Data;
import data.GeneralDefinitions.ClusteringPriorityParameters;
import data.GeneralDefinitions.OrderingParameters;
import data.LUN;
import data.LUNPerTimeUnit;
import data.TimeUnit;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Collections;
import java.util.Map;
import java.util.TreeMap;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

import csvreader.CSVParser;

public class SQLiteDB implements Data {

	private SQLiteUtilityManagement db;
	private String databaseName;

	public static final int f_kbWritten = 1;
	public static final int f_kbRead = 2;
	public static final int f_numOfReads = 3;
	public static final int f_numOfWrites = 4;
	//	public static final int f_ReadCacheHitsPrecentage = 5;
	//	public static final int f_WriteCacheHitsPrecentage = 6;

	Vector<String> insertLunStatements;
	Vector<String> insertTimeUnitStatements;
	Vector<String> insertLunPerTimeUnitStatements;
	private String filename;


	public static final TreeMap<ClusteringPriorityParameters, String> lunPerTimeUnitFields;

	static{
		lunPerTimeUnitFields = new TreeMap<ClusteringPriorityParameters, String>();
		lunPerTimeUnitFields.put(ClusteringPriorityParameters.kbWritten, "kbWritten");
		lunPerTimeUnitFields.put(ClusteringPriorityParameters.kbRead, "kbRead");
		lunPerTimeUnitFields.put(ClusteringPriorityParameters.numOfReads, "numOfReads");
		lunPerTimeUnitFields.put(ClusteringPriorityParameters.numOfWrites, "numOfWrites");
		//		lunPerTimeUnitFields.put(f_ReadCacheHitsPrecentage, "ReadCacheHitsPrecentage");
		//		lunPerTimeUnitFields.put(f_WriteCacheHitsPrecentage, "WriteCacheHitsPrecentage");
	}

	public SQLiteDB(String databaseName) {
		this.databaseName = databaseName;
		db = new SQLiteUtilityManagement(databaseName);
	}

	private int[] convert(Vector<Integer> vec) {
		System.out.println("SQLiteDB, line 59 (ose tzaov)" + databaseName);
		int[] ans = new int[vec.size()];
		for (int i = 0; i < vec.size(); i++) {
			ans[i] = vec.get(i);
		}
		return ans;
	}

	private double[] convert2(Vector<Integer> vec) {
		System.out.println("SQLiteDB, line 59 (ose tzaov)" + databaseName);
		double[] ans = new double[vec.size()];
		for (int i = 0; i < vec.size(); i++) {
			ans[i] = vec.get(i);
		}
		return ans;
	}

	@Override
	public double[] getLUNVector(int LUNid, ClusteringPriorityParameters clusterType, Calendar startDate, Calendar endDate) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:SS");

		Vector<Integer> ans = new Vector<Integer>();
		//get all the time units participating
		ResultSet rs = db.executeQuery(
				"SELECT id FROM " + db.timeUnitTable +
				" WHERE start>'" + sdf.format(startDate.getTime()) + "'"+
				" AND end<'" + sdf.format(endDate.getTime()) + "';");
		System.out.println("SELECT timeunit_id FROM " + db.timeUnitTable +
				" WHERE start>'" + sdf.format(startDate.getTime()) + "'"+
				" AND end<'" + sdf.format(endDate.getTime()) + "';");
		try{
			Vector<Integer> temp = new Vector<Integer>();
			while (rs.next()){
				temp.add(rs.getInt("id"));
			}
			Collections.sort(temp);
			rs.close();
			rs = db.executeQuery(
					"SELECT * FROM " + db.lunPerTimeUnitTable +
					" WHERE lun_id = " + LUNid +
			" ORDER BY timeunit_id ASC;");
			Map<Integer, Integer> tempmap= new TreeMap<Integer,Integer>();
			while(rs.next()){
				int timeunit_id = rs.getInt("timeunit_id");
				int field = rs.getInt(lunPerTimeUnitFields.get(clusterType));
				tempmap.put(timeunit_id, field);
			}
			for (Integer i:temp){
				Integer value = tempmap.get(i);
				if (value == null){
					ans.add(0);
				} else {
					ans.add(value);
				}
			}
		} catch (Exception e){
			e.printStackTrace();
		}
		return convert2(ans);
	}

	@Override
	public long getTotalBytesWritten(int TimeUnitID) {
		ResultSet rs = db.executeQuery(
				"SELECT sum(kbWritten) AS totalBytesWrriten FROM " + db.lunPerTimeUnitTable +
				" WHERE timeunit_id = " + TimeUnitID);
		long totalKBWritten = 0;
		try {
			totalKBWritten = rs.getLong("totalBytesWrriten");
		} catch (SQLException ex) {
			Logger.getLogger(SQLiteDB.class.getName()).log(Level.SEVERE, null, ex);
		}
		return totalKBWritten;
	}

	@Override
	public int[] getUnactiveLUNs(int threshold) {
		Vector<Integer> temp = new Vector<Integer>();
		ResultSet rs = db.executeQuery(
				"SELECT DISTINCT lun_id AS id FROM " + db.lunPerTimeUnitTable +
				" WHERE " + threshold +
				"<(SELECT sum(kbread) FROM " + db.lunPerTimeUnitTable + "  WHERE id=lun_id)");
		try {
			while (rs.next()) {
				temp.add(rs.getInt("id"));
			}
		} catch (SQLException ex) {
			Logger.getLogger(SQLiteDB.class.getName()).log(Level.SEVERE, null, ex);
		}
		return convert(temp);
	}

	@Override
	public int[] getOrderedLUNVector(OrderingParameters orderingParameter) {
		//TODO consider start and end
		Vector<Integer> temp = new Vector<Integer>();
		ResultSet rs = db.executeQuery(
				"SELECT DISTINCT lun_id AS id FROM " + db.lunPerTimeUnitTable +
				" ORDER BY  " + orderingParameter.name());
		try {
			while (rs.next()) {
				temp.add(rs.getInt("id"));
			}
		} catch (SQLException ex) {
			Logger.getLogger(SQLiteDB.class.getName()).log(Level.SEVERE, null, ex);
		}
		return convert(temp);
	}

	@Override
	public LUN getLUN(int LUNid) {
		double size = 0;
		long kbwritten = 0, kbread = 0;
		ResultSet rs = db.executeQuery(
				"SELECT size FROM " + db.lunTable +
				" WHERE id = " + LUNid);
		try {
			size = rs.getDouble("size");
			rs.close();
		} catch (SQLException ex) {
			Logger.getLogger(SQLiteDB.class.getName()).log(Level.SEVERE, null, ex);
		}
		rs = db.executeQuery(
				"SELECT sum(kbwritten) AS kbwritten ,sum(kbread) AS kbread FROM " + db.lunPerTimeUnitTable +
				" WHERE lun_id = " + LUNid);
		try {
			kbread = rs.getLong("kbread");
			kbwritten = rs.getLong("kbwritten");
			rs.close();
		} catch (SQLException ex) {
			Logger.getLogger(SQLiteDB.class.getName()).log(Level.SEVERE, null, ex);
		}
		return new LUN(LUNid, size, kbwritten, kbread);
	}

	@Override
	public TimeUnit getTimeUnit(int TimeUnitID) {
		long kbwritten = 0, kbread = 0;
//		Calendar startTime = null, endTime = null;
		int numReads = 0, numWrites = 0;
//		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:SS");

//		System.out.println(" @ SQLiteDB, getTimeUnit: timeunitID=" + TimeUnitID);
		ResultSet rs = db.executeQuery(
				"SELECT size FROM " + db.timeUnitTable +
				" WHERE  id = " + TimeUnitID);
//		try {
//			System.out.println(" @ SQLiteDB, getTimeUnit: rs is closed " + rs.isClosed());
//		} catch (SQLException ex) {
//			Logger.getLogger(SQLiteDB.class.getName()).log(Level.SEVERE, null, ex);
//		}
//		String temp = "";
//		Date tDate;
		int size=0;
		try {
//			temp = rs.getString("start");
//			startTime = Calendar.getInstance();
//			tDate = sdf.parse(temp);
//			startTime.setTime(tDate);
//			endTime = Calendar.getInstance();
//			temp = rs.getString("end");
//			tDate = sdf.parse(temp);
//			endTime.setTime(tDate);
			size = Integer.parseInt(rs.getString("size"));
			rs.close();
		} catch (SQLException ex) {
			Logger.getLogger(SQLiteDB.class.getName()).log(Level.SEVERE, null, ex);
		}
//		System.out.println("startTime : " + startTime.getTimeInMillis());
		rs = db.executeQuery(
				"SELECT  sum(kbwritten) AS kbwritten ,sum(kbread) AS kbread ," +
				"sum(numOfReads) AS numOfReads ,sum(numOfWrites) AS numOfWrites " +
				"FROM " + db.lunPerTimeUnitTable +
				" WHERE timeunit_id = " + TimeUnitID);
		try {
			kbread = rs.getLong("kbread");
			kbwritten = rs.getLong("kbwritten");
			numReads = rs.getInt("numOfReads");
			numWrites = rs.getInt("numOfWrites");
			rs.close();
		} catch (SQLException ex) {
			Logger.getLogger(SQLiteDB.class.getName()).log(Level.SEVERE, null, ex);
		}
//		return new TimeUnit(TimeUnitID, startTime, endTime, kbwritten, kbread, numReads, numWrites);
		return new TimeUnit(TimeUnitID, size, kbwritten, kbread, numReads, numWrites);
	}

	@Override
	public LUNPerTimeUnit getLUNPerTimeUnit(int TimeUnitID, int LUNid) {
		int kbwritten = 0, kbread = 0;
		int numOfReads = 0, numOfWrites = 0;//, readCacheHitsPrecentage = 0, writeCahceHitsPrecentage = 0;
		LUN lun = this.getLUN(LUNid);
		TimeUnit timeUnit = this.getTimeUnit(TimeUnitID);
		ResultSet rs = db.executeQuery(
				"SELECT * FROM " + db.lunPerTimeUnitTable +
				" WHERE lun_id = " + LUNid +
				" AND timeunit_id = " + TimeUnitID);
		try {
			kbread = rs.getInt(lunPerTimeUnitFields.get(ClusteringPriorityParameters.kbRead));
			kbwritten = rs.getInt(lunPerTimeUnitFields.get(ClusteringPriorityParameters.kbWritten));
			numOfReads = rs.getInt(lunPerTimeUnitFields.get(ClusteringPriorityParameters.numOfReads));
			numOfWrites = rs.getInt(lunPerTimeUnitFields.get(ClusteringPriorityParameters.numOfWrites));
			//			readCacheHitsPrecentage = rs.getInt(lunPerTimeUnitFields.get(ClusteringPriorityParameters.ReadCacheHitsPrecentage));
			//			writeCahceHitsPrecentage = rs.getInt(lunPerTimeUnitFields.get(ClusteringPriorityParameters.WriteCacheHitsPrecentage));
			rs.close();
		} catch (SQLException ex) {
			Logger.getLogger(SQLiteDB.class.getName()).log(Level.SEVERE, null, ex);
		}
		return new LUNPerTimeUnit(lun, timeUnit, kbwritten, kbread, numOfReads, numOfWrites);
	}

	@Override
	public void parse(String filename) {
		this.filename = filename;
		new CSVParser().parseFile(filename, this);
	}

	@Override
	public double getTotalSize() {
		ResultSet rs = db.executeQuery("SELECT sum(size) AS totalSize FROM " + db.lunTable);
		double totalSize = 0;
		try {
			totalSize = rs.getDouble("totalSize");
		} catch (SQLException ex) {
			Logger.getLogger(SQLiteDB.class.getName()).log(Level.SEVERE, null, ex);
		}
		return totalSize;
	}

	@Override
	public int[] getLUNs() {
		Vector<Integer> temp = new Vector<Integer>();

		ResultSet rs = db.executeQuery(
				"SELECT DISTINCT lun_id AS id FROM " + db.lunPerTimeUnitTable + ";");
		try {
			while (rs.next()) {
				temp.add(rs.getInt("id"));
			}
		} catch (SQLException ex) {
			Logger.getLogger(SQLiteDB.class.getName()).log(Level.SEVERE, null, ex);
		}
		return convert(temp);
	}

	@Override
	public int getNumOfTimeUnits() {
		ResultSet rs = db.executeQuery("SELECT count(*) AS numOfTimeUnits FROM " + db.timeUnitTable);
		int numOfTimeUnits = 0;
		try {
			numOfTimeUnits = rs.getInt("numOfTimeUnits");
		} catch (SQLException ex) {
			Logger.getLogger(SQLiteDB.class.getName()).log(Level.SEVERE, null, ex);
		}
		return numOfTimeUnits;
	}

	@Override
	public void addLun(int lun_id, int size) {
		if (insertLunStatements==null){
			insertLunStatements = new Vector<String>();
			insertLunStatements.add("BEGIN TRANSACTION;");
		}
		insertLunStatements.add("INSERT INTO " + db.lunTable + " VALUES( " + lun_id + " ,  " + size + "); ");
		//		db.executeUpdate("INSERT INTO " + db.lunTable + " VALUES( " + lun_id + " ,  " + size + ");");
	}

	@Override
	public void addLunPerTimeUnit(int lun_id, int timeunit_id, double kbWritten, double kbRead, double numOfReads, double numOfWrites) {
		if (insertLunPerTimeUnitStatements==null){
			insertLunPerTimeUnitStatements = new Vector<String>();
			insertLunPerTimeUnitStatements.add("BEGIN TRANSACTION; ");
		}
		insertLunPerTimeUnitStatements.add("INSERT INTO "+db.lunPerTimeUnitTable+" VALUES(" +
				lun_id+","+timeunit_id+","+kbWritten+","+kbRead+","+numOfReads+","+numOfWrites+"); ");

		//       db.execut.eUpdate("INSERT INTO "+db.lunPerTimeUnitTable+" VALUES(" +
		//       		lun_id+","+timeunit_id+","+kbWritten+","+kbRead+","+numOfReads+","+numOfWrites+");");
	}

	@Override
	public void addTimeUnit(int timeUnit_id, int size) {
		if (insertTimeUnitStatements==null){
			insertTimeUnitStatements = new Vector<String>();
			insertTimeUnitStatements.add("BEGIN TRANSACTION; ");
		}
//		insertTimeUnitStatements.add("INSERT INTO " + db.timeUnitTable + "  VALUES( " + timeUnit_id + " , "
//				+ "datetime(" + ((startTime.getTimeInMillis()) / 1000) +
//				",'unixepoch') , datetime( " + ((endTime.getTimeInMillis()) / 1000) + ",'unixepoch') " + "); ");

		insertTimeUnitStatements.add("INSERT INTO " + db.timeUnitTable + "  VALUES( " + timeUnit_id + " , "
				+ size);


		//		db.executeUpdate("INSERT INTO " + db.timeUnitTable + "  VALUES( " + timeUnit_id + " , "
		//				+ "datetime(" + ((startTime.getTimeInMillis()) / 1000) +
		//				 ",'unixepoch') , datetime( " + ((endTime.getTimeInMillis()) / 1000) + ",'unixepoch') " + ");");
	}

	public void createTables() {
		db.createTables();
	}

	@Override
	public void endTransactions() {
		try {
			insertLunStatements.add("COMMIT; ");
			insertTimeUnitStatements.add("COMMIT; ");
			insertLunPerTimeUnitStatements.add("COMMIT; ");
			db.executeTransaction(insertLunStatements);
			db.executeTransaction(insertTimeUnitStatements);
			db.executeTransaction(insertLunPerTimeUnitStatements);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public LUNPerTimeUnit[] getAllLunPerTimeUnits(int timeUnitId, int start, int end, OrderingParameters orderingParameter){
		System.out.println("SQLiteDB, getAllLunPerTimeUnits: Started working");
		int limit = end - start + 1;
		ResultSet rs = db.executeQuery(
				"SELECT "+db.lunPerTimeUnitTable+".*, tmp.size FROM "+db.lunPerTimeUnitTable+
				" JOIN (SELECT DISTINCT id, size FROM "+db.lunTable+
				" JOIN "+db.lunPerTimeUnitTable+" ON id=lun_id" +
				" ORDER BY "+orderingParameter.name()+" LIMIT "+limit+" OFFSET "+start+") as tmp" +
				" ON lun_id=tmp.id WHERE "+db.lunPerTimeUnitTable+".timeunit_id="+timeUnitId+";");
//				"SELECT lunPerTimeUnit.* FROM lunPerTimeUnit " +
//				"JOIN (SELECT id FROM " + db.lunTable +
//				" ORDER BY "+orderingParameter +
//				" LIMIT " + limit + " OFFSET " + start + ") as tmp ON lun_id=tmp.id;");

		LUNPerTimeUnit[] res = new LUNPerTimeUnit[end-start+1];
		int index = 0;
		try {
			while(rs.next()){
				int lunId = rs.getInt("lun_id");
				int timeUnit = rs.getInt("timeunit_id");
				double kbread = rs.getDouble(lunPerTimeUnitFields.get(ClusteringPriorityParameters.kbRead));
				double kbwritten = rs.getDouble(lunPerTimeUnitFields.get(ClusteringPriorityParameters.kbWritten));
				double numOfReads = rs.getDouble(lunPerTimeUnitFields.get(ClusteringPriorityParameters.numOfReads));
				double numOfWrites = rs.getDouble(lunPerTimeUnitFields.get(ClusteringPriorityParameters.numOfWrites));
				int size = rs.getInt("size");
				//			readCacheHitsPrecentage = rs.getInt(lunPerTimeUnitFields.get(ClusteringPriorityParameters.ReadCacheHitsPrecentage));
				//			writeCahceHitsPrecentage = rs.getInt(lunPerTimeUnitFields.get(ClusteringPriorityParameters.WriteCacheHitsPrecentage));

				//TODO this is wrong, either get the real data or change the LUN class
				LUN lun = new LUN(lunId, size, -1, -1);
				LUNPerTimeUnit lptu = new LUNPerTimeUnit(this, lun, timeUnit, kbwritten, kbread, numOfReads, numOfWrites);
				res[index] = lptu;
				index++;
			}
			rs.close();
		} catch (SQLException ex) {
			Logger.getLogger(SQLiteDB.class.getName()).log(Level.SEVERE, null, ex);
		}
		System.out.println("SQLiteDB, getAllLunPerTimeUnits: Finished working");
		return res;
	}
	
	@Override
	public String getFIleName() {
		return filename;
	}

	@Override
	public void addTimeUnit(int timeUnit_id, int size, Calendar d) {
		// TODO Auto-generated method stub
		
	}


	//SHEILTA
	//SELECT lunPerTimeUnit.* FROM lunPerTimeUnit JOIN (SELECT id FROM luns  ORDER BY kbwritten LIMIT 10 OFFSET 1000) as tmp ON lun_id=tmp.id;
	//String s=
	//	"SELECT "+db.lunPerTimeUnitTable+".* FROM "+db.lunPerTimeUnitTable+
	//	" JOIN (SELECT DISTINCT id FROM "+db.lunTable+
	//	" JOIN "+db.lunPerTimeUnitTable+" ON id=lun_id" +
	//	" ORDER BY "+orderingParameter.name()+" LIMIT "+limit+" OFFSET "+start+") as tmp" +
	//	" ON lun_id=tmp.id WHERE "+db.lunPerTimeUnitTable+".id="+timeUnitId+";";
}
